package db;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import model.Contact;
import model.Enterprise;
import model.Person;
import model.type.SubjectType;

public class ContactDAO {
	private static final String TABLE = "contact";	
	private static String onSqlError = "SQL statement fault";
			
	private static final String 
		ID_FIELD = "contact",	
		SUBJECT_FIELD = "subject_fk",
		CONTACT_TYPE_FIELD = "contact_type_fk",
		VALUE_TEXT_FIELD = "value_text",
		ORDER_BY_FIELD = "orderby",
		SUBJECT_TYPE_FIELD = "subject_type_fk",		
		NOTE_FIELD = "note";
	
	private static final String INSERT_STATEMENT_STRING = 
			"INSERT INTO "+TABLE+" " +
			"("+SUBJECT_FIELD+", "+CONTACT_TYPE_FIELD+", " +VALUE_TEXT_FIELD+", "
			+ORDER_BY_FIELD+", "+SUBJECT_TYPE_FIELD+", "+NOTE_FIELD+") " +
			"VALUES (?, ?, ?, ?, ?, ?)";
	
	private static final String UPDATE_STATEMENT_STRING = 
			"UPDATE "+TABLE+" SET " 
			+SUBJECT_FIELD+" = ?, "+CONTACT_TYPE_FIELD+" = ?, "+VALUE_TEXT_FIELD+" = ?, "
			+ORDER_BY_FIELD+" = ?, "+SUBJECT_TYPE_FIELD+" = ?, "+NOTE_FIELD+" = ? " +
			"WHERE "+ID_FIELD+" = ?";
	
	private static final String DELETE_STATEMENT_STRING =
			"DELETE FROM "+TABLE+" WHERE "+ID_FIELD+" = ?";
	
	private static PreparedStatement insertStatement, updateStatement, deleteStatement;
	
	static{
		try {
			insertStatement = ImbiConnection.getConnection().prepareStatement(INSERT_STATEMENT_STRING);
			updateStatement = ImbiConnection.getConnection().prepareStatement(UPDATE_STATEMENT_STRING);
			deleteStatement = ImbiConnection.getConnection().prepareStatement(DELETE_STATEMENT_STRING);
		} catch (SQLException e) {			
			e.printStackTrace();
			System.exit(0);
		}
	}
	
	public Contact find(int id){			
		try{				
			Statement st = ImbiConnection.getConnection().createStatement();
			st.executeQuery("SELECT * FROM "+TABLE+" WHERE "+ID_FIELD+" = "+id);					
			Contact c = extractContact(st.getResultSet()).get(0);
			return c;
		} catch (SQLException | IndexOutOfBoundsException e){
				System.out.println(onSqlError);				
				return null;
		}		
	}

	private ArrayList<Contact> extractContact(ResultSet r) throws SQLException {
		ArrayList<Contact> list = new ArrayList<Contact>();
		while(r.next()){		
			Contact c = new Contact(
				r.getInt(1),
				r.getInt(2),
				r.getInt(3),
				r.getString(4),
				r.getInt(5),
				r.getInt(6),
				r.getString(7)
			);
			list.add(c);
		}
		return list;
	}	
	
	
	public ArrayList<Contact> getContacts(Enterprise e){
		return getContactBySubjectId(e.getId(), SubjectType.ENTERPRISE);
	}
	
	public ArrayList<Contact> getContacts(Person p){
		return getContactBySubjectId(p.getId(), SubjectType.PERSON);
	}
	
	private ArrayList<Contact> getContactBySubjectId(int id, int type){
		try{				
			Statement st = ImbiConnection.getConnection().createStatement();
			st.executeQuery("SELECT * FROM "+TABLE+" WHERE "+SUBJECT_FIELD+
					" = "+id+" AND "+SUBJECT_TYPE_FIELD+" = "+type);					
			return extractContact(st.getResultSet());			
		} catch (SQLException | IndexOutOfBoundsException e){
				System.out.println(onSqlError);				
				return null;
		}		
	}
	
	public void insert(Contact c){
		try{											
		    insertStatement.setInt(1, c.getSubject());
		    insertStatement.setInt(2, c.getContactType());		    
		    insertStatement.setString(3, c.getValueText());
		    insertStatement.setInt(4, c.getOrderby());
		    insertStatement.setInt(5, c.getSubjectType());
		    insertStatement.setString(6, c.getNote());		    
		    insertStatement.executeUpdate();			
		} catch (SQLException s){
				System.out.println(onSqlError);							
		}		
	}
	
	public void update(Contact c){
		try{										
		    updateStatement.setInt(1, c.getSubject());
		    updateStatement.setInt(2, c.getContactType());
		    updateStatement.setString(3, c.getValueText());
		    updateStatement.setInt(4, c.getOrderby());
		    updateStatement.setInt(5, c.getSubjectType());
		    updateStatement.setString(6, c.getNote());		    
		    updateStatement.setInt(7, c.getId());
		    updateStatement.executeUpdate();			
		} catch (SQLException s){
				System.out.println(onSqlError);								
		}			
	}
	
	public void delete(Contact c){
		try{										
		    deleteStatement.setInt(1, c.getId());		    
		    deleteStatement.executeUpdate();			
		} catch (SQLException s){
				System.out.println(onSqlError);					
		}			
	}	
}
